﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using Common;
using Controladores;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Entidades;
using Entidades.jSON.Sistema;
using Import;
using Import.Enums;
using Import.Exceptions;
using Web.Handlers.Data;
using Web.Helpers.Documents.Scripts;
using Table = DocumentFormat.OpenXml.Wordprocessing.Table;

namespace Web.Helpers
{
    public class ScriptHelper
    {
        #region Classes

        public class TableRowTrinity
        {
            public TableRow Previous { get; set; }
            public TableRow Current { get; set; }
            public TableRow Next { get; set; }
            public TableRowTrinity(TableRow current, TableRow previous, TableRow next)
            {
                this.Previous = previous;
                this.Current = current;
                this.Next = next;
            }
        }

        #endregion

        #region Properties

        public static string[] SpecialChars = new string[] { "/", "//", "\"", "\\\\", ":", ",", ".", "…", "-", "–", "“", "”", "¿", "?", "¡", "!" };

        public List<TimeCode> AllTimeCodes { get; set; }

        #endregion

        #region Http Context Methods

        public static void ClearImportCurrentState(HttpContext context)
        {
            Helpers.WebHelper.Session.RemoveValue(WellKnownKeys.Session.IMPORT_PROJECT_INFO, context);
        }

        public static ImportProject GetImportCurrentState(HttpContext context, string filename = null)
        {
            var importProject = Helpers.WebHelper.Session.Get<ImportProject>(WellKnownKeys.Session.IMPORT_PROJECT_INFO, context);
            if ( (String.IsNullOrEmpty(filename)) || (importProject != null && importProject.Data.FileInfo.FileName == filename) )
            {
                return importProject;
            }
            else
            {
                ClearImportCurrentState(context);
                return new ImportProject();
            }
        }

        public static void SaveImportCurrentState(HttpContext context, ImportProject importProject, ImportStep? step = null)
        {
            if (step.HasValue)
                importProject.StepInfo.Step = step.Value;

            Helpers.WebHelper.Session.Set(WellKnownKeys.Session.IMPORT_PROJECT_INFO, importProject, context);
        }

        #endregion

        public static RespuestaInfo GetResponseInfoWithExceptionData(Exception ex)
        {
            var errorMessage = String.Empty;
            var importErrorData = default(ImportErrorData);

            var scriptImportException = ex as ScriptImportException;
            if (scriptImportException != null)
            {
                scriptImportException.ErrorMessage += scriptImportException.Message;

                var headerDataExceptions = new[]
                {
                    typeof (MissingHeaderDataException)
                };

                var exceptionType = scriptImportException.GetType();

                if (headerDataExceptions.Contains(exceptionType))
                {
                    var missingHeaderDataException = (MissingHeaderDataException)ex;
                }

                errorMessage = scriptImportException.ErrorMessage;
                importErrorData = new ImportErrorData(scriptImportException);
            }
            else
            {
                errorMessage = "<b>Ocurrió un error al intentar procesar el documento.</b> " + ex.Message;
            }

            return new RespuestaInfo(false, errorMessage, importErrorData);            
        }

        public static RespuestaInfo ContinueImportProcess(ImportProject importProject, HttpContext context)
        {
            var respuestaInfo = default(RespuestaInfo);

            var importInfo = ScriptHelper.CreateImport(importProject.Data.UploadDirectory, importProject.Data.SavedFileName, importProject.Settings, importProject.HeaderDocumentInfo);
            if (importInfo.Error == null)
            {
                #region Import succeed
                var scriptInfo = importInfo.ScriptInfo;

                // Create script with imported data
                var idGuion = ScriptHelper.CreateScriptWithImportedData(scriptInfo, importProject.Data.IdScript, importProject.Data.IdTranslator);
                if (idGuion != Common.WellKnownKeys.Entities.DEFAULT_ID)
                {
                    // Create upload Directory and copy files from temp directory to it
                    Helpers.FileHelper.CopyFiles(importProject.Data.UploadDirectory, GetUploadDirectory(idGuion.ToString()));

                    var guion = new ControladorBase<Guion>().ObtenerEntidad(idGuion);
                    var importacion = new GuionImportacion
                    {
                        Guion = guion,
                        Fecha = DateTime.Now,
                        Usuario = new ControladorBase<Usuario>().ObtenerEntidad(importProject.Settings.UserId),
                        DOC = scriptInfo.DOC,
                        BRK = scriptInfo.BRK,
                        STR = scriptInfo.STR,
                        Clave = scriptInfo.Keyword,
                        Duracion = scriptInfo.TotalRunningTime,
                        Episodio = scriptInfo.Episode,
                        Loops = scriptInfo.TotalLoops,
                        Paginas = scriptInfo.TotalPages,
                        Personajes = scriptInfo.TotalCharacters,
                        Proyecto = scriptInfo.Project,
                        Titulo = scriptInfo.Title,
                        Traduccion = scriptInfo.Translation
                    };
                    var result = new ControladorBase<GuionImportacion>().GuardarEntidad(importacion);
                    if (result)
                    {
                        respuestaInfo = new RespuestaInfo(true, "Guión generado exitosamente.", guion.Id);

                        ClearImportCurrentState(context);
                    }
                }
                else
                {
                    respuestaInfo = new RespuestaInfo(false, "Ocurrió un error al intentar guardar el Guión importado.");
                }
                #endregion
            }
            else
            {
                #region Import failed
                var errorMessage = importInfo.Error.ErrorMessage;
                var importErrorData = new ImportErrorData(importInfo.Error);

                respuestaInfo = new RespuestaInfo(false, errorMessage, importErrorData);
                #endregion
            }

            return respuestaInfo;
        }

        public static int CreateScriptWithImportedData(ScriptInfo scripInfo, int? idScript, int? idTranslator)
        {
            var idGuion = Common.WellKnownKeys.Entities.DEFAULT_ID;
            try
            {
                var controladorGuion = new ControladorBase<Guion>();
                var controladorGuionEstado = new ControladorBase<GuionEstado>();
                var controladorUsuario = new ControladorBase<Usuario>();
                //var controladorAutorizacion = new ControladorBase<GuionAutorizacion>();
                
                var guion = idScript.HasValue ? controladorGuion.ObtenerEntidad((int)idScript.Value) : new Guion();

                var fechaActual = DateTime.Now;
                var usuarioActual = SessionHelper.GetCurrentUser();
                var estadoGenerado = controladorGuionEstado.ObtenerEntidad(Common.WellKnownKeys.Entities.EstadosGuion.GENERADO.Id);

                var usuario = controladorUsuario.ObtenerEntidad(usuarioActual.Id);

                #region Guion
                guion.Estado = estadoGenerado;
                guion.Proyecto = null;
                guion.Duracion = scripInfo.TotalRunningTime;
                guion.Loops = scripInfo.TotalLoops;
                guion.Paginas = scripInfo.TotalPages;
                guion.Personajes = scripInfo.TotalCharacters;
                guion.Titulo = scripInfo.Title;
                guion.Episodio = scripInfo.Episode;
                guion.Director = null;
                guion.Adaptador = null;
                guion.Traductor = idTranslator.HasValue ? controladorUsuario.ObtenerEntidad(idTranslator.Value) : null;
                guion.Mezclador = null;
                guion.Comentarios = null;
                #endregion

                #region Items del Guion
                if (guion.Items != null)
                    guion.Items.Clear();
                else
                    guion.Items = new List<GuionItem>();
                foreach (var character in scripInfo.CharactersInfo)
                {
                    var guionItem = new GuionItem
                    {
                        Guion           = guion,
                        Personaje       = character.Value.Name,
                        Actor           = null,
                        FechaGrabacion  = new DateTime?(),
                        Loops           = character.Value.Loops,
                        TipoCancion     = null
                    };
                    guion.Items.Add( guionItem );
                }
                #endregion

                #region Costo del Guion
                //guion.Costo = new GuionControlador().CopiarCostoMaestro(new ControladorBase<Costo>().ObtenerEntidad(x => x.TipoProyecto.Id == idTipoProyecto));
                #endregion

                #region Auditoria Guion
                guion.UsuarioCreacion           = usuario;
                guion.FechaCreacion             = fechaActual;
                guion.UsuarioUltimaModificacion = usuario;
                guion.FechaUltimaModificacion   = fechaActual;
                guion.UsuarioTerminacion        = null;
                guion.FechaTerminacion          = null;
                #endregion

                #region Transición de Estados
                GuionControlador.AgregarTransicionDeEstado(guion, WellKnownKeys.Entities.EstadosGuion.GENERADO.Id, fechaActual, usuarioActual.Id, true);
                #endregion

                #region Autorizaciones del Guion
                GuionControlador.AgregarTransicionDeAutorizacion(guion, WellKnownKeys.Entities.AutorizacionesGuion.TRADUCCION.Id, fechaActual, usuarioActual.Id); 
                #endregion

                var success = controladorGuion.GuardarEntidad(guion);
                if (success)
                    idGuion = guion.Id;
            }
            catch (Exception)
            {                
                throw;
            }
            return idGuion;
        }

        public static SourceDocumentInfo GetDocumentHeaderData(string sourceFileName, ImportSettings settings)
        {
            var document = default(WordprocessingDocument);
            var sourceDocumentInfo = default(SourceDocumentInfo);
            try
            {
                document = WordprocessingDocument.Open(sourceFileName, false);
                sourceDocumentInfo = GetHeaderData(document, settings.TablesInfo.Header);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (document != null)
                {
                    document.Close();
                    document.Dispose();
                }
            }
            return sourceDocumentInfo;
        }

        public static ImportInfo CreateImport(string path, string sourceFileName, ImportSettings settings, SourceDocumentInfo sourceDocumentInfo)
        {
            var importInfo  = new ImportInfo();
            
            var document    = default(WordprocessingDocument);
            
            var currentRow  = default(TableRow);
            var prevRow     = default(TableRow);
            var nextRow     = default(TableRow);
            var rowNumber   = 1;

            var contentTableIndexes     = settings.TablesInfo.Content;
            var timeCodeColIndex        = contentTableIndexes.TimeCode.ColIndex;
            var characterColIndex       = contentTableIndexes.Character.ColIndex;
            var dialogueColIndex        = contentTableIndexes.Dialogue.ColIndex;

            try
            {
                var loopsInfo = new List<LoopInfo>();
                var characters = new Dictionary<string, CharacterInfo>();
                var carriedData = new CarriedData();

                document = WordprocessingDocument.Open(sourceFileName, false);

                //var tblHeader       = GetTable(document, this.Settings.TablesInfo.Header.Index);
                var tblPersonajes   = GetTable(document, settings.TablesInfo.Content.Index);

                //var numberOfPages = 0;
                //if (document.ExtendedFilePropertiesPart != null && document.ExtendedFilePropertiesPart.Properties != null && document.ExtendedFilePropertiesPart.Properties.Pages != null)
                //{
                //    numberOfPages = Convert.ToInt32(document.ExtendedFilePropertiesPart.Properties.Pages.Text);
                //}

                //var tblHeaderRows       = GetRows(tblHeader);
                var tblPersonajesRows   = GetRows(tblPersonajes);

                // TODO: ¿Y si runningTime es vacío o NULL? Aquí entra la Validación de PARAMETRO_IMPORTACION
                var runningTimeCode = new TimeCode(sourceDocumentInfo.RunningTime, TimeCode.SourceValueType.Minutes, true);
                var totalRunningTime = Common.DateAndTime.ConvertTimeUnit
                    (
                        DateAndTime.TimeUnits.Minutes,
                        Convert.ToInt32(runningTimeCode.Second),
                        Convert.ToInt32(runningTimeCode.Minute),
                        Convert.ToInt32(runningTimeCode.Hour)
                    );

                var rad = new STR(settings, sourceDocumentInfo.Translator, sourceDocumentInfo.Project, sourceDocumentInfo.Episode, sourceDocumentInfo.OriginalTitle, sourceDocumentInfo.TranslatedTitle, runningTimeCode.ToString());

                #region Process Document
                var currentHour = 0;
                var rows = tblPersonajesRows.ToList();
                if ( rows.Any())
                {
                    var allTimeCodes = new List<TimeCode>();
                    var startingRow = settings.TablesInfo.Content.OmitFirstRow ? 1 : 0;
                    var isFirstRow = true;
                    var isLastRow = false;
                    for(var i=startingRow; i < rows.Count(); i++)
                    {
                        isLastRow = i == rows.Count() -1;
                        currentRow = rows.ElementAt(i);
                        if ( currentRow.Count() > i)
                        {
                            nextRow = rows.ElementAt(i + 1);
                        }
                        if (i > startingRow)
                        {
                            prevRow = rows.ElementAt(i - 1);
                        }
                        ReadCharacter(settings, allTimeCodes, rad, loopsInfo, rowNumber, new TableRowTrinity(currentRow, prevRow, nextRow), characters, ref carriedData, ref currentHour, isFirstRow, isLastRow);
                        rowNumber++;
                        isFirstRow = false;
                    }
                }
                #endregion

                rad.CreateSummaryPage(characters);
                var timeCodes = rad.TimeCodes;

                var brk = new BRK(loopsInfo, timeCodes, characters, sourceDocumentInfo.Project);

                var onlyFileName = new FileInfo(sourceFileName).Name;
                var docFileName = onlyFileName; //sourceFileName;
                sourceFileName  = Path.ChangeExtension(onlyFileName, ""); //Path.ChangeExtension(sourceFileName, "");
                sourceFileName  = sourceFileName.Substring(0, sourceFileName.LastIndexOf('.'));
                var radFileName = sourceFileName + " [STR].pdf"; // TODO => revisit this
                var brkFileName = sourceFileName + " [BRK].pdf"; // TODO => revisit this
                
                rad.SaveToFile(path, radFileName);
                brk.SaveToFile(path, brkFileName);

                importInfo.ScriptInfo = new ScriptInfo()
                {
                    DOC                 = docFileName,
                    BRK                 = brkFileName,
                    STR                 = radFileName,
                    CharactersInfo      = characters,
                    TotalCharacters     = characters.Count,
                    TotalLoops          = characters.Sum(x => x.Value.Loops),
                    TotalPages          = rad.TotalCount.PageCount,
                    TotalRunningTime    = totalRunningTime,
                    Keyword             = sourceDocumentInfo.Keyword,
                    Project             = sourceDocumentInfo.Project,
                    Title               = sourceDocumentInfo.TranslatedTitle,
                    Episode             = Convert.ToInt32(String.IsNullOrEmpty(sourceDocumentInfo.Episode) ? "0" : sourceDocumentInfo.Episode),
                    Translation         = sourceDocumentInfo.Translator
                };
            }
            catch (ScriptImportException ex)
            {
                #region Exception handling

                var currentPage = GetPageNumber(currentRow);

                var dialogueExceptions = new[]
                {
                    typeof (EmtpyRowException)
                };
                var timeCodeExceptions = new[]
                {
                    typeof (UnexpectedTimeCodeException),
                    typeof (MissingTimeCodeException),
                    typeof (InvalidFormatTimeCodeException)
                };

                var exceptionType = ex.GetType();

                if (dialogueExceptions.Contains(exceptionType))
                {
                    #region Dialogue Exceptions
                    ex.ErrorMessage += String.Format("Se detectó una fila vacía (sin datos) en la página <b>{0}</b>.", currentPage);
                    #endregion
                }

                if (timeCodeExceptions.Contains(exceptionType))
                {
                    #region TimeCode Exceptions

                    var timeCode    = GetTableCellText(currentRow, timeCodeColIndex);
                    var character   = GetTableCellText(currentRow, characterColIndex);
                    var dialogue    = GetTableCellText(currentRow, dialogueColIndex);

                    ex.ErrorMessage = String.Format("Página <b>{0}</b>, Diálogo N° <b>{1}</b> - ", currentPage, rowNumber);
                    if (ex is MissingHeaderDataException)
                    {
                        ex.DialogueInfo1 = new ScriptImportException.DialogueExceptionInfo(timeCode, character, dialogue);
                        ex.ErrorMessage += "Se detectó un TimeCode faltante";
                    }
                    if (ex is UnexpectedTimeCodeException)
                    {
                        ex.DialogueInfo2 = new ScriptImportException.DialogueExceptionInfo(timeCode, character, dialogue);

                        timeCode    = GetTableCellText(prevRow, timeCodeColIndex);
                        character   = GetTableCellText(prevRow, characterColIndex);
                        dialogue    = GetTableCellText(prevRow, dialogueColIndex);

                        ex.DialogueInfo1 = new ScriptImportException.DialogueExceptionInfo(timeCode, character, dialogue);

                        ex.ErrorMessage +=
                            String.Format(
                                "Se detectó una inconsistencia en la secuencia de TimeCodes, pues luego de <b>{0}</b> figura <b>{1}</b>, siendo menor/anterior",
                                ex.DialogueInfo1.TimeCode, ex.DialogueInfo2.TimeCode);
                    }
                    if (ex is MissingTimeCodeException)
                    {
                        ex.DialogueInfo1 = new ScriptImportException.DialogueExceptionInfo(timeCode, character, dialogue);
                        ex.ErrorMessage += "Se detectó un TimeCode faltante";
                    }
                    if (ex is InvalidFormatTimeCodeException)
                    {
                        ex.DialogueInfo1 = new ScriptImportException.DialogueExceptionInfo(timeCode, character, dialogue);
                        ex.ErrorMessage +=
                            String.Format(
                                "Se detectó un TimeCode con formato inválido: '<b>{0}</b>' (formato esperado: hh:mm:ss / mm:ss)",
                                ex.DialogueInfo1.TimeCode);
                    }

                    #endregion
                }

                #endregion

                importInfo.Error = ex;
            }
            finally
            {
                if (document != null)
                {
                    document.Close();
                    document.Dispose();
                }
            }
            return importInfo;
        }

        public static SourceDocumentInfo GetHeaderData(WordprocessingDocument document, ImportSettings.HeaderTable headerTable)
        {
            var sourceDocumentInfo = new SourceDocumentInfo();
            var headerDataExceptionInfo  = default(MissingCellInfo);
            var currentHeaderTableValues = new CellInfo[0]; 
            try
            {
                // TODO : estoy asumiendo el TableIndex a partir de uno de los campos, pero podría no ser el correcto?

                currentHeaderTableValues = ReadTable(document, headerTable.Translator.TableIndex);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.Translator, HeaderData.Translator, null);
                sourceDocumentInfo.Translator = GetTableCellText(document, headerTable.Translator);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.Keyword, HeaderData.Keyword, null);
                sourceDocumentInfo.Keyword = GetTableCellText(document, headerTable.Keyword);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.Project, HeaderData.Project, null);
                sourceDocumentInfo.Project = GetTableCellText(document, headerTable.Project);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.Episode, HeaderData.Episode, null);
                sourceDocumentInfo.Episode = GetTableCellText(document, headerTable.Episode);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.OriginalTitle, HeaderData.OriginalTitle, null);
                sourceDocumentInfo.OriginalTitle = GetTableCellText(document, headerTable.OriginalTitle);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.TranslatedTitle, HeaderData.TranslatedTitle, null);
                sourceDocumentInfo.TranslatedTitle = GetTableCellText(document, headerTable.TranslatedTitle);

                headerDataExceptionInfo = new MissingCellInfo(headerTable.RunningTime, HeaderData.RunningTime, null);
                sourceDocumentInfo.RunningTime = GetTableCellText(document, headerTable.RunningTime);
            }
            catch (ArgumentOutOfRangeException ex)
            {
                if (headerDataExceptionInfo != null && currentHeaderTableValues != null)
                {
                    var missingHeaderDataException =
                        new Exception(
                            String.Format(
                                "Ocurrió un error al intentar leer del encabezado el campo <b>{0}</b> en la fila <b>{1}</b>, columna <b>{2}</b>.",
                                ((HeaderData) headerDataExceptionInfo.ExpectedObject).GetDescription(),
                                headerDataExceptionInfo.CellInfo.RowIndex,
                                headerDataExceptionInfo.CellInfo.ColIndex),
                            ex);
                    throw new MissingHeaderDataException(missingHeaderDataException, currentHeaderTableValues);
                }
                else
                {
                    throw new Exception();
                }
            }
            return sourceDocumentInfo;
        }

        public static string GetUploadDirectory(string directory)
        {
            return String.Format("{0}/{1}/", Common.WellKnownKeys.Directories.Files.ScriptsUploaded, directory);
        }

        private static void ReadCharacter(ImportSettings settings, List<TimeCode> allTimeCodes, STR str, List<LoopInfo> loopsInfo, int rowNumber, TableRowTrinity rows, Dictionary<string, CharacterInfo> characters, ref CarriedData carriedData, ref int currentHour, bool isFirstRow, bool isLastRow)
        {
            try
            {
                var timeCodeColIndex    = settings.TablesInfo.Content.TimeCode.ColIndex;
                var characterColIndex   = settings.TablesInfo.Content.Character.ColIndex;
                var dialogueColIndex    = settings.TablesInfo.Content.Dialogue.ColIndex;

                var characterTimeCode   = GetTableCellText( rows.Current, timeCodeColIndex );
                var characterNames      = GetTableCellText( rows.Current, characterColIndex );
                var characterDialogue   = GetTableCellTextFormatted( rows.Current, dialogueColIndex );

                var timeCode        = default(TimeCode);
                var prevTimeCode    = default(TimeCode);
                var nextTimeCode    = default(TimeCode);

                var emptyRowData = ValidateEmptyRowData(new string[] {characterTimeCode, characterNames, characterDialogue});
                if (emptyRowData)
                {
                    if (settings.Validations[ImportSettings.ImportValidations.EmptyRow])
                        throw new EmtpyRowException(null);
                    else
                        return;
                }

                var validateHourReset = settings.Validations[ImportSettings.ImportValidations.HourResetTimeCode];
                if (validateHourReset && CheckIfHourReset(settings, characterTimeCode))
                {
                    if (!isFirstRow)
                    {
                        currentHour++; // An hour reset annotation has been found => increase current hour counter
                        return;
                    }
                    else
                    {
                        // If it's the first row, consider Character & TimeCode, and disregard Hour Reset logic.
                    }
                }

                var validateMissingTimeCode = settings.Validations[ImportSettings.ImportValidations.MissingTimeCode];
                var lastValidTimeCode = allTimeCodes.LastOrDefault() ?? new TimeCode();
                try
                {
                    timeCode = new TimeCode(characterTimeCode);
                    if ( currentHour > Convert.ToInt32(timeCode.Hour) )
                        TimeCode.SetHour(timeCode, currentHour);
                }
                catch (MissingTimeCodeException)
                {
                    if (validateMissingTimeCode)
                        throw;
                    timeCode = lastValidTimeCode;
                }

                #region Next & Prev TimeCodes

                if (rows.Next != null)
                {
                    try
                    {
                        // If next row's TimeCode is an hour reset annotation, just ignore it, otherwise consider it.
                        var nextCharacterTimeCode = GetTableCellText(rows.Next, timeCodeColIndex);
                        if ( !CheckIfHourReset(settings, nextCharacterTimeCode) )
                            nextTimeCode = new TimeCode(nextCharacterTimeCode);
                    }
                    catch (MissingTimeCodeException)
                    {
                        if (validateMissingTimeCode)
                            throw;
                    }
                }

                //if (rows.Previous != null)
                //{
                //    try
                //    {
                //        prevTimeCode = new TimeCode(GetTableCellText(rows.Previous, timeCodeColIndex));
                //    }
                //    catch (MissingTimeCodeException)
                //    {
                //        if (validateMissingTimeCode)
                //            throw;
                //        prevTimeCode = lastValidTimeCode;
                //    }
                //}
                if (allTimeCodes.Any())
                {
                    prevTimeCode = allTimeCodes.Last();
                }
                    
                if (prevTimeCode != null)
                {
                    if (!timeCode.IsGreaterOrEqualThan(prevTimeCode))
                        throw new UnexpectedTimeCodeException(null);
                }

                #endregion

                allTimeCodes.Add(timeCode);

                ProcessCharacter(settings, loopsInfo, characters, ref carriedData, timeCode, nextTimeCode, characterNames, characterDialogue, isLastRow);

                //var characterTextInfo = new Helpers.OfficeHelper.TextInfo( row.Cells[dialogueColIndex].Range );   
                //var dialogueInfo = new DialogueInfo();
                //foreach (var textInfoCharacter in characterTextInfo.Characters)
                //{
                //    var charInfo = new DialogueInfo.CharInfo()
                //    {
                //        Character = textInfoCharacter.Character,
                //        Bold = textInfoCharacter.Bold,
                //        Italic = textInfoCharacter.Italic,
                //        Underline = textInfoCharacter.Underline
                //    };
                //    dialogueInfo.Characters.Add( charInfo );
                //}
                ///rad.AddDialogue(timeCode, characterNames, dialogueInfo, row.IsLast);
                str.AddDialogue(timeCode, characterNames, characterDialogue, isLastRow);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static bool CheckIfHourReset(ImportSettings settings, string characterTimeCode)
        {
            var hourResetAnnotation = settings.Annotations[ImportSettings.ImportAnnotations.HourReset][0];
            return hourResetAnnotation == characterTimeCode;
        }

        private static bool ValidateEmptyRowData(string[] rowData)
        {
            //return (string.IsNullOrEmpty(characterTimeCode) &&
            //        string.IsNullOrEmpty(characterNames) &&
            //        string.IsNullOrEmpty(characterDialogue));
            return rowData.All(x => String.IsNullOrEmpty(x.Trim()));
        }

        private static void ProcessCharacter(ImportSettings settings, List<LoopInfo> loopsInfo, Dictionary<string, CharacterInfo> characters, ref CarriedData carriedData, TimeCode characterTimeCode, TimeCode nextTimeCode, string characterGroup, string characterDialogue, bool isLastDialogue)
        {
            try
            {
                var currentCharacters = new List<CharacterInfo>();
                foreach (var name in characterGroup.Split('/'))
                {
                    var characterName = name.Trim();

                    var character = !characters.ContainsKey(characterName) ? new CharacterInfo() : characters[characterName];
                        character.Name = characterName;
                        character.TimeCodes.Add( characterTimeCode );
                        character.AppareanceOrder =  character.AppareanceOrder < 1 ? characters.Count + 1 : character.AppareanceOrder;
                
                    if ( !characters.ContainsKey(characterName) )
                        characters.Add( characterName,  character );
                    else
                        characters[characterName] = character;

                    currentCharacters.Add( character );
                }

                var annotations = settings.Annotations[ImportSettings.ImportAnnotations.Director];

                characterDialogue = RemoveSpecialChars(characterDialogue, annotations[0], annotations[1]);
                characterDialogue = RemoveExtraWhitespaces(characterDialogue);

                var loopInfoCount = loopsInfo.Count;

                //var currentTimeCode = currentCharacters.Last().TimeCodes.Last();

                if (settings.LoopParsers.ContainsKey(ImportSettings.LoopParserTypes.ByWords))
                {
                    var words = settings.LoopParsers[ImportSettings.LoopParserTypes.ByWords];
                    CheckLoopForAmountOfWords(loopsInfo, ref carriedData, currentCharacters, characterDialogue, isLastDialogue, words);
                }

                //If there's Carried Data and loop count increased during current TimeCode, set it as Last Loop TimeCode
                if ( carriedData.IsNotNull() && (loopsInfo.Count > loopInfoCount) )
                    carriedData.LastLoopTimeCode = currentCharacters.Last().TimeCodes.Last();

                if (nextTimeCode != null && loopInfoCount == loopsInfo.Count)
                {
                    if (settings.LoopParsers.ContainsKey(ImportSettings.LoopParserTypes.ByWords))
                    {
                        var time = settings.LoopParsers[ImportSettings.LoopParserTypes.ByTime];
                        var isMaxElapsedTimeReached = CheckLoopForAmountOfTime(ref carriedData, nextTimeCode, time);
                        if (isMaxElapsedTimeReached)
                        {
                            if ( carriedData.CharactersInfo != null )
                            {
                                foreach (var carriedCharacter in carriedData.CharactersInfo.Values)
                                {
                                    carriedCharacter.Character.Loops += 1;
                                }
                            }
                            AddLoop(loopsInfo, currentCharacters, ref carriedData, false);
                            carriedData = null;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void CheckLoopForAmountOfWords(List<LoopInfo> loopsInfo, ref CarriedData carriedData, List<CharacterInfo> characters, string characterDialogue, bool isLastDialogue, int wordsForLoop)
        {
            try
            {
                //var allCarriedWords                     = carriedData.IsNotNull() && carriedData.CharactersInfo.IsNotNull() && carriedData.CharactersInfo.Count > 0 ? carriedData.CharactersInfo.Sum(x => x.Value.Words) : 0;
                var allCarriedWords = carriedData.IsNotNull() && carriedData.CharactersInfo.IsNotNull() && carriedData.CharactersInfo.Count > 0 ? carriedData.Words : 0;
                var remainingWordsToCompleteNextLoop    = wordsForLoop - allCarriedWords;
                var remainingWordsInText                = CountWords( characterDialogue );
                var characterLoopCount                  = 0;
                var fullLoops                           = Convert.ToInt32( Math.Floor( (remainingWordsInText + allCarriedWords) / Convert.ToDecimal(wordsForLoop) ) );
                var isLoopCompleted                     = false;

                //var currentTimeCode                     = characters.Last().TimeCodes.Last();

                while ( (remainingWordsInText >= remainingWordsToCompleteNextLoop) && (fullLoops > 0) )
                {
                    fullLoops--;

                    var wordsReadInfo = ReadNextLoop(ref characterDialogue, remainingWordsToCompleteNextLoop, wordsForLoop);
                    if (wordsReadInfo.IsLoopComplete)
                    {
                        characterLoopCount++;

                        AddLoop(loopsInfo, characters, ref carriedData);

                        UpdateCharactersLoopsWithCarriedData(ref carriedData, characters);
                    }

                    remainingWordsToCompleteNextLoop    = wordsReadInfo.RemainingWordsToCompleteNextLoop;
                    remainingWordsInText                = wordsReadInfo.RemainingWordsInText;
                    isLoopCompleted                     = wordsReadInfo.IsLoopComplete;

                    if ( remainingWordsInText == 0 )
                        break;
                }

                foreach (var character in characters)
                {
                    character.Loops += characterLoopCount;
                }

                if ( remainingWordsInText > 0 || isLoopCompleted == false )
                    AddToCarriedData(ref carriedData, characters, remainingWordsInText);

                if (isLastDialogue)
                {
                    UpdateCharactersLoopsWithCarriedData(ref carriedData, null);
                    AddLoop(loopsInfo, characters, ref carriedData);
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void AddLoop(List<LoopInfo> loopsInfo, List<CharacterInfo> characters, ref CarriedData carriedData, bool isLoopByWordCount = true)
        {
            try
            {
                TimeCode timeCode = null;

                var currentTimeCode = characters.Last().TimeCodes.Last();

                if (carriedData == null)
                    timeCode = characters.First().TimeCodes.Last();

                var characterNames = new List<string>();

                if (carriedData != null && carriedData.CharactersInfo != null && carriedData.CharactersInfo.Count > 0 )
                {
                    characterNames.AddRange( carriedData.CharactersInfo.Select(x => x.Key) );

                    var t1 = carriedData.CharactersInfo.First().Value.Character.TimeCodes.Last();
                    var t2 = carriedData.FirstTimeCode;
                    // Aquí no puede haber TimeCode exceptions porque t1 y t2 ya son objetos TimeCodes..
                    var timeCodes = new TimeCode[] { t1, t2 };
                    TimeCode.Sort(timeCodes);
                    timeCode = timeCodes.First();
                }

                if ( timeCode == null )
                    timeCode = currentTimeCode;
            
                if ( isLoopByWordCount )
                {
                    foreach (var character in characters)
                    {
                        if ( !characterNames.Contains(character.Name) )
                            characterNames.Add( character.Name );
                    }
                }

                loopsInfo.Add( new LoopInfo() { Number = loopsInfo.Count + 1, TimeCode = timeCode, CharacterNames = characterNames } );
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void AddToCarriedData(ref CarriedData carriedData, List<CharacterInfo> characters, int remainingWordsInText)
        {
            var currentTimeCode = characters.First().TimeCodes.Last();

            if (carriedData.IsNull())
                carriedData = new CarriedData();

            if (carriedData.FirstTimeCode.IsNull())
                carriedData.FirstTimeCode = currentTimeCode;

            if ( carriedData.CharactersInfo != null )
            {
                foreach (var character in characters)
                {
                    //if (!carriedData.CharactersInfo.ContainsKey(character.Name))
                    //    carriedData.CharactersInfo.Add(character.Name, new CarriedData.CarriedInfo() { Character = character, Words = remainingWordsInText });
                    //else
                    //    carriedData.CharactersInfo[character.Name].Words += remainingWordsInText;
                    if (!carriedData.CharactersInfo.ContainsKey(character.Name))
                        carriedData.CharactersInfo.Add(character.Name, new CarriedData.CarriedInfo() { Character = character});
                }
                carriedData.Words += remainingWordsInText;
            }

            //if (carriedData.LastTimeCode.IsNull())
                carriedData.LastTimeCode = currentTimeCode;            
        }

        private static void UpdateCharactersLoopsWithCarriedData(ref CarriedData carriedData, List<CharacterInfo> characters = null)
        {
            if (carriedData.IsNotNull() && carriedData.CharactersInfo.IsNotNull() && carriedData.CharactersInfo.Count > 0)
            {
                foreach (var carriedCharacter in carriedData.CharactersInfo.Values)
                {
                    if ( characters == null )
                        carriedCharacter.Character.Loops += 1;

                    if ( characters != null )
                    {
                        foreach (var character in characters)
                        {
                            if ( carriedCharacter.Character.Name != character.Name )
                                carriedCharacter.Character.Loops += 1;
                        }
                    }
                }
            }
            carriedData = null;            
        }

        private static bool CheckLoopForAmountOfTime(ref CarriedData carriedData, TimeCode currentTimeCode, int time)
        {
            var isMaxElapsedTimeReached = false;

            var checkElapsedTime = true;

            if (carriedData.IsNotNull() && carriedData.FirstTimeCode != null) //&& carriedData.CharactersInfo != null && carriedData.CharactersInfo.Count > 0)
            {
                var firstCarriedTimeCode = carriedData.FirstTimeCode;
                if (carriedData.LastLoopTimeCode != null)
                    checkElapsedTime = !TimeCode.AreEqual(carriedData.LastLoopTimeCode, carriedData.FirstTimeCode);
                
                if ( checkElapsedTime && GetElapsedTime(firstCarriedTimeCode, currentTimeCode) > time )
                {
                    //foreach (var carriedCharacter in carriedData.CharactersInfo.Values)
                    //{
                    //    carriedCharacter.Character.Loops += 1;
                    //}             
                    //carriedData = null;
                    isMaxElapsedTimeReached = true;
                }
            }
            return isMaxElapsedTimeReached;
        }

        public static int GetElapsedTime(TimeCode time1, TimeCode time2)
        {
            var t1 = new TimeSpan(Convert.ToInt32(time1.Hour), Convert.ToInt32(time1.Minute), Convert.ToInt32(time1.Second));
            var t2 = new TimeSpan(Convert.ToInt32(time2.Hour), Convert.ToInt32(time2.Minute), Convert.ToInt32(time2.Second));
            var t3 = t2.Subtract(t1);
            return Convert.ToInt32(t3.TotalSeconds);
        }

        private static WordsReadInfo ReadNextLoop(ref string text, int remainingWordsToCompleteNextLoop, int wordsForLoop)
        {
            var remainingWordsInText = 0;

            var wordCount = CountWords( text );

            //When Character's last words are exactly <<wordsForLoop>>, so add 1 Loop and end Character's line.
            if (remainingWordsToCompleteNextLoop == 0)
                remainingWordsToCompleteNextLoop = wordsForLoop;

            var wordsToBeRemoved = remainingWordsToCompleteNextLoop;

            var isLoopComplete = false;
            if (wordCount >= remainingWordsToCompleteNextLoop)
            {
                remainingWordsInText = wordCount - remainingWordsToCompleteNextLoop;
                remainingWordsToCompleteNextLoop = remainingWordsInText > wordsForLoop ? wordsForLoop : wordsForLoop - remainingWordsInText;
                isLoopComplete = true;
            }
            else
            {
                remainingWordsInText = 0;
                remainingWordsToCompleteNextLoop = remainingWordsToCompleteNextLoop - wordCount;
                isLoopComplete = false;
            }

            RemoveWords(ref text, wordsToBeRemoved);

            var wordsReadInfO = new WordsReadInfo()
            {
                IsLoopComplete                      = isLoopComplete,
                WordsRead                           = wordCount,
                RemainingWordsToCompleteNextLoop    = remainingWordsToCompleteNextLoop,
                RemainingWordsInText                = remainingWordsInText
            };

            return wordsReadInfO;
        }

        private static int CountWords(string text)
        {
            //return text.Split(' ').Length;
            text = text.TrimStart().TrimEnd();
            var count = 0;
            var index = 0;
            while ( index < text.Length )
            {
               //check if current char is part of a word
               while ( index < text.Length && Char.IsWhiteSpace(text[index]) == false )
                   index++;
               count++;
               //skip whitespace until next word
               while ( index < text.Length && Char.IsWhiteSpace(text[index]) == true )
                   index++;
            }
            return count;
        }
        private static string Replace(string text, string find, string replace)
        {
            return text.Replace(find, replace);
        }
        public static string RemoveSpecialChars(string text, string annotationStart, string annotationEnd)
        {
            text = SpecialChars.Aggregate(text, (current, chr) => Replace(current, chr, " "));
            text = ScriptHelper.RemoveAnnotations(text, annotationStart, annotationEnd);
            return text;
        }
        private static string Remove(string text, string remove)
        {
            return text.Replace(remove, String.Empty);
        }
        //private static string RemoveBetween(string text, char begin, char end)
        //{
        //    var regex = new Regex(string.Format("\\{0}.*?\\{1}", begin, end));
        //    return regex.Replace(text, string.Empty);
        //    //return new Regex(" +").Replace(regex.Replace(text, string.Empty), " "); //avoid duplicate empty spaces
        //}
        //public static bool IsStartingAnnotation(string text)
        //{
        //    return text.TrimStart().StartsWith(AnnotationStart.ToString());
        //}
        //public static bool IsEndingAnnotation(string text)
        //{
        //    return text.TrimEnd().EndsWith(AnnotationEnd.ToString());
        //}
        public static bool HasStartingAnnotation(string text, string annotationStart)
        {
            return text.Contains(annotationStart);
        }
        public static bool HasEndingAnnotation(string text, string annotationEnd)
        {
            return text.Contains(annotationEnd);
        }
        //public static string RemoveAnnotations(string text)
        //{
        //    if (text.Contains(AnnotationStart) && text.Contains(AnnotationEnd))
        //    {
        //        var start   = text.IndexOf(AnnotationStart);
        //        var end     = text.IndexOf(AnnotationEnd) + 1;
        //        text = text.Replace( text.Substring(start, end - start), String.Empty);
        //        RemoveAnnotations(text);
        //    }
        //    return text;
        //}
        public static string RemoveAnnotations(string text, string annotationStart, string annotationEnd)
        {
            return RemoveBetween(text, annotationStart, annotationEnd);
        }
        private static string RemoveBetween(string text, string begin, string end)
        {
            var regex = new Regex(string.Format("\\{0}.*?\\{1}", begin, end));
            return regex.Replace(text, string.Empty);
            //return new Regex(" +").Replace(regex.Replace(text, string.Empty), " "); //avoid duplicate empty spaces
        }
        public static void RemoveWords(ref string text, int amount)
        {
            var reducedText = String.Empty;
            text = text.TrimStart();
            text = text.TrimEnd();
            var arrWords = text.Split(' ');
            for (var i = 0; i < amount; i++)
            {
                reducedText += arrWords[i] + " ";
            }
            reducedText = reducedText.TrimStart();
            reducedText = reducedText.TrimEnd();
            text = text.Replace(reducedText, String.Empty);
        }
        //public static void RemoveWords(ref string text, int amount)
        //{
        //    var allText = "";
        //    var wordCount = 0;
        //    var charIndex = 0;
        //    while ( charIndex < text.Length || wordCount < amount )
        //    {
        //        //check if current char is part of a word
        //        while (charIndex < text.Length && Char.IsWhiteSpace(text[charIndex]) == false)
        //        {
        //            allText += text[charIndex];
        //            charIndex++;
        //        }
                    
        //        wordCount++;

        //        //skip whitespace until next word
        //        while (charIndex < text.Length && Char.IsWhiteSpace(text[charIndex]) == true)
        //        {
        //            allText += text[charIndex];
        //            charIndex++;
        //        }

        //        if (wordCount == amount)
        //            //break;
        //            charIndex = text.Length;
        //    }

        //    if (wordCount == amount)
        //    {
        //        if ( allText.Length > 0 )
        //            text = text.Replace(allText, "");
        //    }
        //}
        public static string RemoveExtraWhitespaces(string text)
        {
            return Regex.Replace(text, @"\s+", " ");            
        }
        public static string ReplaceSpecialChars(string text, string[] specialChars, string replacement)
        {
            if ( specialChars.Any( x => text.Contains(x) ) )
            {
                //foreach (var chr in specialChars)
                //{
                //    text = text.Replace(chr, replacement);
                //}
                text = specialChars.Aggregate(text, (current, chr) => current.Replace(chr, replacement));
                ReplaceSpecialChars(text, specialChars, replacement);
            }
            return text;
        }
        public static string ReplaceSpecialChars(string text, Dictionary<string, string> charsAndReplacements)
        {
            //foreach (var chr in charsAndReplacements)
            //{
            //    text = text.Replace(chr.Key, chr.Value);
            //}
            //return text;
            return charsAndReplacements.Aggregate(text, (current, chr) => current.Replace(chr.Key, chr.Value));
        }

        public static CellInfo[] ReadTable(WordprocessingDocument document, int tableIndex)
        {
            var tableCells = new List<CellInfo>();
            var table = GetTable(document, tableIndex);
            var rows  = GetRows(table);
            var cells = GetCells(table, rows.First());
            for (var rowIndex = 1; rowIndex < rows.Count +1; rowIndex++)
            {
                for(var colIndex = 1; colIndex < cells.Count() +1; colIndex++)
                {
                    var cellValue = GetTableCellText(document, tableIndex, rowIndex, colIndex);
                    if ( cellValue != null )
                        tableCells.Add( new CellInfo(false, tableIndex, rowIndex, colIndex, cellValue));
                }
            }
            return tableCells.ToArray();
        }
        public static Table GetTable(WordprocessingDocument document, int tableIndex)
        {
            var table = default(Table);
            var tables = document.MainDocumentPart.Document.Descendants<Table>().ToList();
            if (tables.Any() && tables.Count >= tableIndex)
                table = tables[tableIndex - 1];
            return table;
        }
        public static List<TableRow> GetRows(Table table)
        {
            return table.Elements<TableRow>().ToList();
        }
        public static List<TableCell> GetCells(Table table, TableRow row)
        {
            return row.Descendants<TableCell>().ToList();
            //return row.Where(x => x.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.TableCell)).Cast<TableCell>().ToList();
        }
        public static TableRow GetRow(IEnumerable<TableRow> rows, int rowIndex)
        {
             return rows.ElementAt(rowIndex - 1);
        }
        public static TableCell GetCell(TableRow row, int colIndex)
        {
            var cell = default(TableCell);
            if (row != null)
            {
                var cells = row.Descendants<TableCell>().ToList();
                if (cells.Any())
                {
                    if (cells.Count() > colIndex - 1)
                        cell = cells.ElementAt(colIndex - 1);
                }
            }
            return cell;
        }
        public static string GetTableCellText(IEnumerable<TableRow> rows, int rowIndex, int colIndex)
        {
            var row  = GetRow(rows, rowIndex);
            var cell = GetCell(row, colIndex);
            return cell != null ? cell.InnerText : null;
        }
        public static string GetTableCellText(TableRow row, int colIndex)
        {
            var cell = GetCell(row, colIndex);
            return cell != null ? cell.InnerText : null;
        }
        public static string GetTableCellTextFormatted(TableRow row, int colIndex)
        {
            //var list = new List<DocumentFormat.OpenXml.OpenXmlElement>();
            //var cell = row.Descendants<TableCell>().ElementAt(colIndex - 1);
            //var paragraphs = cell.Where(x => x.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Paragraph));
            //foreach (var paragraph in paragraphs)
            //{
            //    list.AddRange( paragraph.ChildElements.Where(x => x.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Run)) );
            //}
            //var text = String.Join(" ", list.Select(x => x.InnerText));

            var sb = new StringBuilder();
            var cell = row.Descendants<TableCell>().ElementAt(colIndex - 1);
            var paragraphs = cell.Where(x => x.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Paragraph));
            foreach (var paragraph in paragraphs)
            {
                sb.Append( " " );
                var childs = paragraph.ChildElements.Where(x => x.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Run));
                foreach (var child in childs)
                {
                    foreach (var c in child.ChildElements)
                    {
                        if (c.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.RunProperties))
                        {
                        }
                        if ( c.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Break) )
                        {
                            sb.Append( " " );
                        }
                        if ( c.GetType() == typeof (DocumentFormat.OpenXml.Wordprocessing.Text) )
                        {
                            sb.Append( c.InnerText );
                        }
                    }
                }
            }
            var text = sb.ToString();

            return text;
        }
        public static string GetTableCellText(WordprocessingDocument document, int tableIndex, int rowIndex, int colIndex)
        {
            var table = GetTable(document, tableIndex);
            var rows  = GetRows(table);
            return GetTableCellText(rows, rowIndex, colIndex);
        }
        public static string GetTableCellText(WordprocessingDocument document, CellInfo cellInfo)
        {
            return cellInfo.Enabled
                ? GetTableCellText(document, cellInfo.TableIndex, cellInfo.RowIndex, cellInfo.ColIndex)
                : String.Empty;
        }

        public static int GetPageNumber(OpenXmlElement element)
        {
            //<w:br w:type="page"/> 
            //<w:lastRenderedPageBreak/>
            var pageNumber = 1;
            try
            {
                var parent = default(OpenXmlElement);
                    parent = element.Parent;
                var root =  default(OpenXmlElement);
                while (parent != null)
                {
                    root = parent;
                    parent = parent.Parent;
                }
                
                var tmpElem = element;
                while (tmpElem != root)
                {
                    var sibling = tmpElem.PreviousSibling();
                    while (sibling != null)
                    {
                        pageNumber += sibling.Descendants<LastRenderedPageBreak>().Count();
                        sibling = sibling.PreviousSibling();
                    }
                    tmpElem = tmpElem.Parent;
                }            
            }
            catch (Exception)
            {
                pageNumber = 1;
            }
            return pageNumber;
        }
    }
}